﻿<cfcomponent 
			displayname="clrmonitoring" 
			hint="Functions for clearance monitoring are here" 
			output="false"
>
   
    <cffunction name="gridtoexcel"
				returntype="string"
				access="remote"
    >
    
    	<cfargument name = "token">
    
    	<cfquery name="mygriddata"
				 datasource="IBOSE_GLOBAL"
	    >
	    	SELECT * 
			  FROM EGINCLRMAIN
		</cfquery>
   
   		<cfspreadsheet  
	    action="write" 
	    filename = "#expandpath('./')#ClearanceData.xls"
	    overwrite = "true"				  
	    query = "mygriddata" >
		
		<cfreturn token>
   
    </cffunction> 
    
    <cffunction
			name="saveclearancedata"
			returnformat="plain" 
			access="remote"
			returntype="string"
    >
    
    <cfargument name="token">
    
    <cfset referencenumber = trim(form.referencenumber) >
	
	
	<cfset findres = findnocase("date", form.field) >
		
		
	<cfif findres GT 0 >
		<cfset value = left(form.value, 10)>
		<cfquery name="saveacell" datasource="IBOSE_GLOBAL" >
			UPDATE EGINCLRMAIN
			   SET #trim(form.field)# = <cfqueryparam cfsqltype="cf_sql_date" value="#value#" >
			 WHERE ID = '#referencenumber#'
			 ;
		</cfquery>
	<cfelse>
		<cfquery name="saveacell" datasource="IBOSE_GLOBAL" >
			UPDATE EGINCLRMAIN
			   SET #trim(form.field)# = <cfqueryparam value="#form.value#" >
			 WHERE ID = '#referencenumber#'
			 ;
		</cfquery>
    </cfif>
	
	
	<cfreturn
			token
	>
	
	</cffunction>



	<cffunction
			name="getrecipients"
			returntype="void"
			
	>
	<cftry>
		
		<cfquery name="qrygroup" datasource="IBOSE_GLOBAL">
			SELECT EMAILADDRESS
			  FROM EGLKCLRDEPT
		     WHERE THEGROUP = '#form.thegroup#';  
		</cfquery>
        
        <cfquery name="qrytemplate" datasource="IBOSE_GLOBAL" maxrows="1">
                SELECT BODYTPL, SUBJECTTPL, DATELASTVISIT
                  FROM EGLKEMAILTEMPLATE
             <cfif trim(form.thetemplate) NEQ "no template" >
                 WHERE NAME = '#form.thetemplate#'
             </cfif>
              ORDER BY DATELASTVISIT DESC;  
		</cfquery>
        
       <cfloop query="qrytemplate">
       		<cfset datenow = dateformat(now(), 'YYYY-MM-DD') >
            <cfset timenow = timeformat(now(), 'HH:mm:ss') >
            <cfset urdate  = datenow & ' ' & timenow >
       
            <cfquery name="qrytemplateB" datasource="IBOSE_GLOBAL" maxrows="1">
                UPDATE EGLKEMAILTEMPLATE
                   SET DATELASTVISIT = <cfqueryparam cfsqltype="cf_sql_datetime" value="#urdate#" >
                 WHERE NAME = '#form.thetemplate#'
                ;  
            </cfquery>
       </cfloop>
	
	<cfif isdefined("qrygroup") >
		
        <cfif isdefined("qrytemplate") >
			<cfset bodytemplate =  qrytemplate.BODYTPL >
            <cfset subjecttemplate =  qrytemplate.SUBJECTTPL >
        <cfelse>
        	<cfset bodytemplate =  "" >
            <cfset subjecttemplate =  "" >
        </cfif>
		
		
		
		<cfoutput>
		{
		"success": "true",
		"data": {
				<cfset ecounter = 1 >
				<cfset querylen = qrygroup.recordcount >
				<cfset theemail = "" >
				<cfloop query="qrygroup">
					<cfset theemail = theemail & EMAILADDRESS >
					<cfif querylen NEQ ecounter> 
						<cfset theemail = theemail & ", " >
				    </cfif>
				    <cfset ecounter = ecounter + 1 >
				</cfloop>
				"recipients": #serializeJSON(theemail)#,
				"subject":    #serializeJSON("subject test")#,
				"body":       #serializeJSON("<b>Leonell Lagumbay</b>")#,
				"bodytemplate":       #serializeJSON(bodytemplate)#,
				"subjecttemplate":       #serializeJSON(subjecttemplate)#
		}
		}
		</cfoutput>
	<cfelse>
		<cfoutput>
		{
		"success": "true",
		"data": {
				
				"recipients": #serializeJSON("no email found")#,
				"subject": #serializeJSON("no subject found")#,
				"body": #serializeJSON("")#,
				"bodytemplate":       #serializeJSON("")#,
				"subjecttemplate":       #serializeJSON("")#
		}
		}
		</cfoutput>
	</cfif>
		<cfcatch>
		<cfoutput>
		{
		"success": "true",
		"data": {
				
				"recipients": #serializeJSON("")#,
				"subject": #serializeJSON(cfcatch.Message)#,
				"body": #serializeJSON(cfcatch.Detail)#,
				"bodytemplate":       #serializeJSON("")#,
				"subjecttemplate":       #serializeJSON("")#
		}
		}
		</cfoutput>
		</cfcatch>
	</cftry>
	</cffunction>
	
	
	
	<cffunction
			name="emailtoclearingdept"
			returntype="void"
			
	>
	<cftry>
	
	<cfset thegroup = trim(form.thegroup) >
	<cfset therecipient = trim(form.therecipient) >
	<cfset thesubject = trim(form.thesubject) >
	<cfset thebody = trim(form.thebody) >
	<cfset theid = trim(form.theid) >
	
	<cfquery name="isrecordexist" datasource="IBOSE_GLOBAL" >
		SELECT IDFK
	      FROM EGINCLREMAIL
	     WHERE IDFK = '#theid#' AND EMAILADDRESS = '#therecipient#';  
	</cfquery>
	
	<cfset autogencode = right(createuuid(), 5) >
	
	<cfif isdefined("isrecordexist") >
		<cfif isrecordexist.recordcount GT 0 >
			<cfquery name="isrecordexist" datasource="IBOSE_GLOBAL" >
				UPDATE EGINCLREMAIL
			       SET POSTCODE = <cfqueryparam cfsqltype="cf_sql_varchar" value="#autogencode#" >
				 WHERE IDFK = '#theid#' AND EMAILADDRESS = '#therecipient#';  
			</cfquery>
		<cfelse>
			<cfquery name="isrecordexist" datasource="IBOSE_GLOBAL" >
				INSERT INTO EGINCLREMAIL (CLREMAILID,
				                          IDFK,
				                          CLEARINGGROUP,
				                          ACCOUNTABILITY,
				                          EMAILADDRESS,
				                          POSTCODE
				                          )
				                   VALUES(
				                         <cfqueryparam cfsqltype="cf_sql_varchar" value="#createuuid()#" >,
										 <cfqueryparam cfsqltype="cf_sql_varchar" value="#theid#" >,
										 <cfqueryparam cfsqltype="cf_sql_varchar" value="#thegroup#" >,
										 <cfqueryparam cfsqltype="cf_sql_varchar" value="" >,
										 <cfqueryparam cfsqltype="cf_sql_varchar" value="#therecipient#" >,
										 <cfqueryparam cfsqltype="cf_sql_varchar" value="#autogencode#" >
				                   );
			</cfquery>
		</cfif>
	<cfelse>
		<cfquery name="isrecordexist" datasource="IBOSE_GLOBAL" >
			INSERT INTO EGINCLREMAIL (CLREMAILID,
			                          IDFK,
			                          CLEARINGGROUP,
			                          ACCOUNTABILITY,
			                          EMAILADDRESS,
			                          POSTCODE
			                          )
			                   VALUES(
			                         <cfqueryparam cfsqltype="cf_sql_varchar" value="#createuuid()#" >,
									 <cfqueryparam cfsqltype="cf_sql_varchar" value="#theid#" >,
									 <cfqueryparam cfsqltype="cf_sql_varchar" value="#thegroup#" >,
									 <cfqueryparam cfsqltype="cf_sql_varchar" value="" >,
									 <cfqueryparam cfsqltype="cf_sql_varchar" value="#therecipient#" >,
									 <cfqueryparam cfsqltype="cf_sql_varchar" value="#autogencode#" >
			                   );
		</cfquery>	
	</cfif>
	
	<cfset answer = findnocase("xxxSummary of Accountabilitieszzz", thebody) >
	<cfif answer LT 1 >
		<cfset newbody = replace(thebody, "xxxautogeneratedcodezzz", autogencode, "all") >  
		<cfset newbody = replace(newbody, "xxxdonotdeleteyyyzzz", therecipient, "all") >
		<cfset newbody = replace(newbody, "xxxgroupyyyzzz", thegroup, "all") >
		<cfset newbody = replace(newbody, "xxxdomainzzz", "http://localhost:8501/test10/cfbose/", "all") >
		<cfset newbody = replace(newbody, "xxxidzzz", theid, "all") >
	<cfelse>
		<cfset tabletop = "<table style='border-collapse: collapse' border='1'><tbody><tr> <th>Type</th><th>Remark</th><th>Details</th><th>Email</th><th>Timestamp</th></tr><th>">
		<cfset tablecenter = "" >
		<cfset tablebottom = "</tbody></table>" >
		
		
		<cfquery name="qryclrdepartmenta" datasource="IBOSE_GLOBAL">
			SELECT CLEARINGGROUP, EMAILADDRESS
		      FROM EGINCLREMAIL
		     WHERE IDFK = '#theid#';
		</cfquery>
		
		<cfloop query="qryclrdepartmenta" >
			<cfquery name="qryclrdepartment" datasource="IBOSE_GLOBAL" maxrows="1">
				SELECT CLEARINGDEPT, 
					   THEGROUP, 
					   EMAILADDRESS, 
					   DATAINDEXSUFFIX
			      FROM EGLKCLRDEPT
			     WHERE THEGROUP = '#qryclrdepartmenta.CLEARINGGROUP#' AND DATAINDEXSUFFIX IS NOT NULL AND EMAILADDRESS = '#qryclrdepartmenta.EMAILADDRESS#';
			</cfquery>
			
			<cfquery name="qryclrmain" datasource="IBOSE_GLOBAL" maxrows="1">
				SELECT DATEREPLIED#qryclrdepartment.DATAINDEXSUFFIX# AS DATEREPLIED, REMARKS#qryclrdepartment.DATAINDEXSUFFIX# AS REMARKS, DETAIL#qryclrdepartment.DATAINDEXSUFFIX# AS DETAIL
			      FROM EGINCLRMAIN
			     WHERE ID = '#theid#';
			</cfquery>
			<cfif isdefined("qryclrdepartment") >
			<cfset tablecenter = "#tablecenter# <tr><td>#qryclrdepartment.CLEARINGDEPT#</td><td>#qryclrmain.REMARKS#</td><td>#qryclrmain.DETAIL#</td><td>#qryclrdepartmenta.EMAILADDRESS#</td><td>#dateformat(qryclrmain.DATEREPLIED, 'MM/DD/YYYY')#</td></tr>" >
			</cfif>	
			
		</cfloop>
		
		<cfset newbody = replace(thebody, "xxxSummary of Accountabilitieszzz", "#tabletop##tablecenter##tablebottom#") >
	</cfif>
	
	<cfmail
			from="Clearance Team"
			to="#therecipient#"
			subject="#thesubject#"
			type="html"
	>
	<cfoutput>#newbody#</cfoutput>
	</cfmail>
	
	
	
	
	<cfquery name="updateClearance" datasource="IBOSE_GLOBAL" >
		UPDATE EGINCLRMAIN
		<cfif answer LT 1 >
	       SET DATEEMAILTOCLR = <cfqueryparam cfsqltype="cf_sql_date" value="#dateformat(now(), 'YYYY-MM-DD')#" >
		<cfelse>
		   SET DATEENDORSEDTP = <cfqueryparam cfsqltype="cf_sql_date" value="#dateformat(now(), 'YYYY-MM-DD')#" >
		</cfif>
		 WHERE ID = '#theid#';
	</cfquery>
	
		
	<cfoutput>
		{
			"success": true,
			"data": {
				"detail": "success"
			}
		}
	</cfoutput>
		
		
		<cfcatch>
			<cfoutput>
				{
					"success": true,
					"data": {
						"detail": "#cfcatch.Detail# #cfcatch.Message#"
					}
				}
			</cfoutput>
		</cfcatch>
	</cftry>
	</cffunction>

</cfcomponent>